Trabalho Prático de Introdução a Banco de Dados - Dota 2

Nomes:

Gabriel Henrique Souto Pires
Raydan Elias Gaspar

Tema

Dota 2 é um jogo eletrônico do gênero Multiplayer Online Battle Arena (MOBA, "arena de batalha multijogador online"), também conhecido como Action Real-Time Strategy (ARTS, "estratégia de ação em tempo real") onde o jogador controla um personagem em uma batalha entre dois times, cujo objetivo é derrotar a equipe adversária e, para isso, é, indispensavelmente, necessário destruir o Ancestral (Ancient), que, por sua vez, se localiza no centro da base inimiga. Cada base é defendida por torres e ondas de unidades (chamadas de creeps) que percorrem os caminhos principais do mapa (chamadas de trilhas, ou lanes) que levam à suas bases. Existem 3 trilhas no mapa do jogo, que por convenção são chamadas de trilha de cima (Top, atravessa a parte de cima do mapa), trilha do meio (Mid ou Middle, caminho que atravessa o centro do mapa) e trilha de baixo (Bot ou Bottom, caminho que atravessa a parte de baixo do mapa).

Na imagem abaixo podemos ver o minimapa do Dota2 e ao lado um diagrama representando o layout de um jogo do gênero MOBA, onde os pontos roxos e rosas representam as torres de cada time, com exceção do maior ponto localizado nos cantos da imagem dentro de cada base, que representa o Ancient.


Figura 1: Minimapa do Dota 2 (esquerda) e layout padrão de jogos MOBA (direita)

Dados

Os dados foram coletados utilizando a API não oficial do jogo, chamada OpenDoTA, nela coletamos dados de várias partidas do ano de 2015, assim como os dados dos jogadores presentes em cada partida e todas as suas ações, como por exemplo as coordenadas das posições que o jogador passou durante a partida, o herói que escolheu, quantas vezes morreu, entre outros. Os dados coletados foram disponibilizados em formato json, e não em formato de tabelas como precisávamos, então foi preciso selecionar os dados que queríamos entre aqueles fornecidos pela API e montar um banco de dados a partir deles.

Uma característica do formato json é usar o tipo abstrato de dados dicionário, que é composto de pares de chaves e valores, onde cada chave pode conter uma lista de outras chaves e seus valores, isso faz com que existam várias "sub-tabelas" nos dados, o que impossibilita a importação dos dados diretamente para um banco de dados convencional. Dessa forma, foram criadas tabelas separadas para cada conjunto de valores do json, sendo que cada tabela referencia o seu "pai" original através de chaves estrangeiras, dessa forma conseguimos manter a relação dos dados como era originalmente feito no json.

Um exemplo dos dados pode ser visto na imagem abaixo.

Figura 2: Amostra dos dados coletados em formato json

Alguns dados não podiam ser buscados através da API, como por exemplo os nomes e números identificadores de cada personagem ou item do jogo, que são informações constantes, que não mudam entre as partidas, mas precisávamos dessas informações uma vez que a maior parte dos dados é numérica, e não seria interessante ter apenas números, mas sim nomes e significados para que os dados fizessem mais sentido. Por fim encontramos esses dados também em formato json, compatíveis com os dados da API, o que fez com que ficasse mais natural a relação entre as informações.

A imagem abaixo mostra um exemplo dos dados constantes de itens do jogo, com o id e nome de cada item listado em formato json.

Figura 3: Dados constantes do jogo, servem de referência para os dados das partidas

Criação do banco de dados

Para criar o banco de dados baseado nas informações em json, primeiro escolhemos quais partes dos dados seriam utilizadas, uma vez que a quantidade de dados é muito extensa, e ficaria inviável modelar e inserir os dados em sua totalidade em um banco de dados devido à limitações de tempo. Depois disso foi decidido o que se tornaria tabela e o que se tornaria atributo. Seguimos um padrão no qual todas as chaves do json que possuíam uma lista de valores, se tornariam tabelas, e essas tabelas seriam relacionadas à tabela acima na hierarquia por meio de chaves estrangeiras. Usando como referência os dados da Figura 2, podemos ver que a parte referente aos dados da partida possui uma chave "players", que por sua vez possui várias informações pontuais e tambem possui chaves com listas e outras informações. Cada uma dessas chaves que possuem mais de um valor seriam candidatas para serem transformadas em tabelas, como pode ser visto no exemplo da imagem abaixo, onde a chave referente à partida (match) possui uma chave com vários valores chamada "players", nesse caso players se tornou uma tabela separada de match, relacionada à match por uma chave estrangeira.

Figura 4: Recorte do diagrama e dos dados para ilustrar o mapeamento do json para banco de dados relacional

Por fim optamos por criar 20 tabelas, que contém desde informações das ações dos jogadores em cada partida, até o nome dos heróis e itens do jogo, passando claro pelas informações das partidas como time vencedor, duração em segundos, entre outros.

Diagrama entidade-relacionamento

Para a criação do diagrama entidade-relacionamento, utilizamos o software brModelo, que foi indicado pela professora em sala durante a discussão do trabalho final. O brModelo usa a notação do livro do Prof. Heuser, que é um pouco diferente da que foi vista em sala de aula, mas achamos bem intuitivo e conseguimos modelar o banco sem muitos problemas.

Abaixo podemos ver o diagrama criado para o nosso banco.

Figura 5: Diagrama entidade-relacionamento

Esquema relacional

Para criar o esquema relacional, tivemos vários problemas com algumas ferramentas como o site erdplus.com, também indicado pela professora, e ao tentar utilizar ferramentas mais manuais como o site draw.io, vimos que levaria muito mais tempo do que nós tínhamos disponível. Por esse motivo, optamos por utilizar uma ferramenta mais profissional, no caso o MySQL Workbench, isso agilizou bastante o processo e nos ajudou a não cometer erros no diagrama, uma vez que essa é uma ferramenta especializada em banco de dados.

Para armazenar os dados, optamos por utilizar a biblioteca sqlite3 do python, que foi mostrada na aula ministrada pela nossa monitora, já que é mais prático manter os dados em um arquivo ao invés de utilizar um servidor MySQL. Uma limitação do MySQL Workbench era exportar o modelo apenas no padrão SQL do MySQL, e após alguns testes, vimos que esse padrão não era compatível com a sintaxe do SQLite. Para resolver esse problema, utilizamos um plugin do MySQL Workbench chamado ExportSQLite, que exporta o modelo relacional criado diretamente para código SQL compatível com SQLite.

Após feita a modelagem e criação do código em SQL para criação do banco, começamos a utilizar o Jupyter-Notebook para ler os dados dos arquivos json

Figura 6: Esquema relacional

Código

In [2]:
import numpy as np
import pandas as pd
from matplotlib import pyplot as plt
import seaborn as sns
import io
import sqlite3
import json

from IPython.core.display import display, HTML
display(HTML("<style>.container { width:80% !important; }</style>"))

Dados

In [ ]:
with open('dota2_2800_partidas.json', 'r', encoding="utf8") as game_data:
    json_data = json.load(game_data)

Criação do BD

In [3]:
conn = sqlite3.connect('SQL/dota2_2800_partidas.db')
cursor = conn.cursor()
In [ ]:
f = io.open('SQL/Banco_Dota2.sql', 'r')
sql = f.read()
cursor.executescript(sql)

Inserção dos dados

In [ ]:
#Dados do lobby_type.json
with open('json/lobby_type.json') as lobby_type_json:
    lobby_type_data = json.load(lobby_type_json)
    for l_id in lobby_type_data:
        lobby_type_id = lobby_type_data[l_id]['id']
        lobby_type_name = lobby_type_data[l_id]['name']

        cursor.execute('''INSERT INTO lobby_type (id,name) VALUES(?,?)''', (lobby_type_id,lobby_type_name))

#Dados do game_mode.json
with open('json/game_mode.json') as game_mode_json:
    game_mode_data = json.load(game_mode_json)
    for g_id in game_mode_data:
        game_mode_id = game_mode_data[g_id]['id']
        game_mode_name = game_mode_data[g_id]['name']

        cursor.execute('''INSERT INTO game_mode (id,name) VALUES(?,?)''', (game_mode_id,game_mode_name))

#Dados do order_types.json
with open('json/order_types.json') as order_types_json:
    order_types_data = json.load(order_types_json)
    for o_id in order_types_data:
        order_type_id = o_id
        order_type_name = order_types_data[o_id]

        cursor.execute('''INSERT INTO order_types (id,name) VALUES(?,?)''', (order_type_id,order_type_name))

#Dados do gold_reasons.json
with open('json/gold_reasons.json') as gold_reasons_json:
    gold_reasons_data = json.load(gold_reasons_json)
    for gold_id in gold_reasons_data:
        gold_reasons_id = gold_id
        gold_reasons_name = gold_reasons_data[gold_id]['name']

        cursor.execute('''INSERT INTO gold_reasons (id,name) VALUES(?,?)''', (gold_reasons_id,gold_reasons_name))

#Dados do xp_reasons.json
with open('json/xp_reasons.json') as xp_reasons_json:
    xp_reasons_data = json.load(xp_reasons_json)
    for x_id in xp_reasons_data:
        xp_reasons_id = x_id
        xp_reasons_name = xp_reasons_data[x_id]

        cursor.execute('''INSERT INTO xp_reasons (id,name) VALUES(?,?)''', (xp_reasons_id,xp_reasons_name))

#Dados do item_ids.json
with open('json/item_ids.json') as item_ids_json:
    item_ids_data = json.load(item_ids_json)
    for i_id in item_ids_data:
        item_ids_id = i_id
        item_ids_name = item_ids_data[i_id]

        cursor.execute('''INSERT INTO item_ids (id,name) VALUES(?,?)''', (item_ids_id,item_ids_name))

#Dados do heroes.json
with open('json/heroes.json') as heroes_json:
    heroes_data = json.load(heroes_json)
    for h_id in heroes_data:
        hero_id = heroes_data[h_id]['id']
        hero_name = heroes_data[h_id]['name']
        hero_localized_name = heroes_data[h_id]['localized_name']
        primary_attr = heroes_data[h_id]['primary_attr']
        attack_type = heroes_data[h_id]['attack_type']

        cursor.execute('''INSERT INTO heroes (hero_id,name,localized_name,primary_attr,attack_type) 
        VALUES(?,?,?,?,?)''', (hero_id,hero_name,hero_localized_name,primary_attr,attack_type))
    
conn.commit()
In [ ]:
#match
for match in json_data:
    match_id                = match['match_id']
    radiant_win             = match['radiant_win']
    duration                = match['duration']
    tower_status_dire       = match['tower_status_dire']
    tower_status_radiant    = match['tower_status_radiant']
    barracks_status_radiant = match['barracks_status_radiant']
    barracks_status_dire    = match['barracks_status_dire']
    first_blood_time        = match['first_blood_time']
    lobby_type              = match['lobby_type'] 
    human_players           = match['human_players']
    game_mode               = match['game_mode'] 

    cursor.execute('''INSERT INTO match (match_id,game_mode, lobby_type, radiant_win, duration, 
    human_players, tower_status_dire, tower_status_radiant, barracks_status_radiant,
    barracks_status_dire, first_blood_time) VALUES(?,?,?,?,?,?,?,?,?,?,?)''',
                   (match_id,game_mode, lobby_type, radiant_win, duration, human_players, tower_status_dire, 
                    tower_status_radiant, barracks_status_radiant, barracks_status_dire, first_blood_time))
    
#chat        
    for chat_message in match['chat']:
        chat_match_id = match_id 
        time  = chat_message['time']
        type_ = chat_message['type']
        unit  = chat_message['unit']
        key   = chat_message['key']
        slot  = chat_message['slot']

        cursor.execute('''INSERT INTO chat (chat_match_id,ROWID,time,type,unit,key,slot) VALUES(?,?,?,?,?,?,?)''', 
                       (chat_match_id,None,time,type_,unit,key,slot))
    
    for player in match['players']:
        player_match_id = match_id
        player_slot   = player['player_slot']
        level         = player['level']
        hero_healing  = player['hero_healing']
        leaver_status = player['leaver_status']
        last_hits     = player['last_hits']
        denies        = player['denies']
        gold_per_min  = player['gold_per_min']
        gold_spent    = player['gold_spent']
        gold          = player['gold']
        xp_per_min    = player['xp_per_min']
        hero_id       = player['hero_id']
        hero_damage   = player['hero_damage']
        tower_damage  = player['tower_damage']
        kills         = player['kills']
        deaths        = player['deaths']
        assists       = player['assists']
        item_0        = player['item_0']
        item_1        = player['item_1']
        item_2        = player['item_2']
        item_3        = player['item_3']
        item_4        = player['item_4']
        item_5        = player['item_5']
        
        # Ignorar os Bots (tem account_id = None)
        account_id    = player['account_id']
        if account_id is None: continue 
            
        cursor.execute('''INSERT INTO player (player_match_id,gold_spent,gold,xp_per_min,level,hero_id,
        hero_healing,hero_damage,leaver_status,tower_damage,last_hits,kills,denies,deaths,gold_per_min,
        item_0,item_1,item_2,item_3,item_4,item_5,assists,player_slot) 
        VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', 
                       (player_match_id,gold_spent,gold,xp_per_min,level,hero_id,
        hero_healing,hero_damage,leaver_status,tower_damage,last_hits,kills,denies,deaths,gold_per_min,
        item_0,item_1,item_2,item_3,item_4,item_5,assists,player_slot))
        
        for action in player['actions']:
            action_match_id = match_id 
            action_player_slot = player_slot 
            action_id = action
            action_count = player['actions'][action]

            cursor.execute('''INSERT INTO actions (action_match_id,action_player_slot,action_id,action_count) 
            VALUES(?,?,?,?)''', (action_match_id,action_player_slot,action_id,action_count))
            
        for x_coord in player['lane_pos']:
            for y_coord in player['lane_pos'][x_coord]:
                pos_match_id = match_id
                pos_player_slot = player_slot
                x_pos = x_coord
                y_pos = y_coord
                pos_count = player['lane_pos'][x_coord][y_coord]

                cursor.execute('''INSERT INTO lane_pos (pos_match_id,pos_player_slot,x_pos,y_pos,pos_count)
                VALUES(?,?,?,?,?)''', (pos_match_id,pos_player_slot,x_pos,y_pos,pos_count))
                
        for x_coord in player['obs']:
            for y_coord in player['obs'][x_coord]:
                obs_match_id  = match_id
                obs_player_slot = player_slot
                x_pos         = x_coord
                y_pos         = y_coord
                obs_count     = player['obs'][x_coord][y_coord]

                cursor.execute('''INSERT INTO obs (obs_match_id,obs_player_slot,x_pos,y_pos,obs_count)
                VALUES(?,?,?,?,?)''', (obs_match_id,obs_player_slot,x_pos,y_pos,obs_count))
        
        for x_coord in player['sen']:
            for y_coord in player['sen'][x_coord]:
                sen_match_id  = match_id
                sen_player_slot = player_slot
                x_pos         = x_coord
                y_pos         = y_coord
                sen_count     = player['sen'][x_coord][y_coord]

                cursor.execute('''INSERT INTO sen (sen_match_id,sen_player_slot,x_pos,y_pos,sen_count)
                VALUES(?,?,?,?,?)''', (sen_match_id,sen_player_slot,x_pos,y_pos,sen_count))
                
        for player_gold_reasons in player['gold_reasons']:
            gold_match_id = match_id
            gold_player_slot = player_slot
            gold_id = player_gold_reasons
            gold_count = player['gold_reasons'][gold_id]

            cursor.execute('''INSERT INTO player_gold_reasons (gold_match_id,gold_player_slot,
            gold_id,gold_count) 
            VALUES(?,?,?,?)''', (gold_match_id,gold_player_slot,gold_id,gold_count))
            
        for player_xp_reasons in player['xp_reasons']:
            xp_match_id = match_id
            xp_player_slot = player_slot
            xp_id = player_xp_reasons
            xp_count = player['xp_reasons'][xp_id]

            cursor.execute('''INSERT INTO player_xp_reasons (xp_match_id,xp_player_slot,
            xp_id,xp_count) 
            VALUES(?,?,?,?)''', (xp_match_id,xp_player_slot,xp_id,xp_count))

        for player_damage in player['damage']:
            damage_match_id = match_id
            damage_player_slot = player_slot
            unit_damaged = player_damage
            damage_count = player['damage'][unit_damaged]

            cursor.execute('''INSERT INTO damage (damage_match_id,damage_player_slot,unit_damaged,damage_count) 
            VALUES(?,?,?,?)''', (damage_match_id,damage_player_slot,unit_damaged,damage_count))
        
        for player_healing in player['healing']:
            healing_match_id = match_id
            healing_player_slot = player_slot
            unit_healed = player_healing
            healing_count = player['healing'][unit_healed]

            cursor.execute('''INSERT INTO healing (healing_match_id,healing_player_slot,unit_healed,healing_count) 
            VALUES(?,?,?,?)''', (healing_match_id,healing_player_slot,unit_healed,healing_count))
            
        for player_killed in player['killed']:
            killed_match_id = match_id
            killed_player_slot = player_slot
            unit_killed = player_killed
            killed_count = player['killed'][unit_killed]
            
            cursor.execute('''INSERT INTO killed (killed_match_id,killed_player_slot,unit_killed,killed_count) 
            VALUES(?,?,?,?)''', (killed_match_id,killed_player_slot,unit_killed,killed_count))
        
        for item_uses in player['item_uses']:
            item_match_id = match_id
            item_player_slot = player_slot
            item_name = item_uses
            item_count = player['item_uses'][item_name]

            cursor.execute('''INSERT INTO item_uses (item_match_id,item_player_slot,item_name,item_count) 
            VALUES(?,?,?,?)''', (item_match_id,item_player_slot,item_name,item_count))
        
        for kill in player['kills_log']:
            kills_log_match_id = match_id
            kills_log_player_slot = player_slot
            time = kill['time']
            key = kill['key']
            
            cursor.execute('''INSERT INTO kills_log (kills_log_match_id,kills_log_player_slot,ROWID,time,key) 
            VALUES(?,?,?,?,?)''', (kills_log_match_id,kills_log_player_slot,None,time,key))
conn.commit()

Consultas realizadas

Sabemos que as tabelas principais são 'match' e 'player', vamos ver como os dados nessas tabelas estão distribuídos.

Primeiro vamos analisar a tabela das partidas.

In [22]:
df_match = pd.read_sql_query('''SELECT * FROM match''', conn)

print('Tamanho da tabela match:', df_match.shape)
df_match.describe()
Tamanho da tabela match: (2856, 11)
Out[22]:
match_id game_mode lobby_type radiant_win duration human_players tower_status_dire tower_status_radiant barracks_status_radiant barracks_status_dire first_blood_time
count 2.856000e+03 2856.000000 2856.000000 2856.000000 2856.000000 2856.000000 2856.000000 2856.000000 2856.000000 2856.000000 2856.000000
mean 2.001266e+09 11.110294 3.314426 0.518207 2420.041667 9.980742 987.857843 1046.056373 35.870798 35.850490 103.202381
std 5.818412e+04 10.124103 3.448782 0.499756 683.281324 0.309760 943.530362 947.662790 29.678094 29.127626 93.890709
min 2.001166e+09 1.000000 0.000000 0.000000 120.000000 5.000000 0.000000 0.000000 0.000000 0.000000 0.000000
25% 2.001218e+09 1.000000 0.000000 0.000000 1992.750000 10.000000 0.000000 0.000000 0.000000 0.000000 15.000000
50% 2.001263e+09 4.000000 1.000000 1.000000 2374.000000 10.000000 455.000000 1584.000000 51.000000 51.000000 88.000000
75% 2.001315e+09 22.000000 7.000000 1.000000 2844.250000 10.000000 1974.000000 1974.000000 63.000000 63.000000 156.000000
max 2.001401e+09 22.000000 7.000000 1.000000 5382.000000 10.000000 2047.000000 2047.000000 63.000000 63.000000 542.000000

Podemos ver que temos 2856 partidas no banco, então podemos esperar cerca de 10 vezes esse número na tabela de players, já que em geral são 10 por partida. Podemos notar também que o time Radiant ganha em pouco mais da metade das partidas. Outro dado importante é que existem partidas com bots na base, uma vez que a coluna 'human_players' não tem média igual a 10.

Agora vamos analisar os dados da tabela de jogadores.

In [23]:
df_player = pd.read_sql_query('''SELECT * FROM player''', conn)

pd.set_option('display.max_columns', 23)
print('Tamanho da tabela player:', df_player.shape)
df_player.describe()
Tamanho da tabela player: (28505, 23)
Out[23]:
player_match_id gold_spent gold xp_per_min level hero_id hero_healing hero_damage leaver_status tower_damage last_hits kills denies deaths gold_per_min item_0 item_1 item_2 item_3 item_4 item_5 assists player_slot
count 2.850500e+04 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.00000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000 28505.000000
mean 2.001266e+09 13506.508332 1830.354885 435.537485 17.505631 51.739028 456.025083 11603.238274 0.06234 1252.982319 123.232661 6.980810 4.771759 7.254341 404.192493 97.505631 99.503245 92.472408 87.609577 80.930644 69.257920 11.062691 66.011226
std 5.814627e+04 6488.119730 1711.936343 148.238260 4.655382 33.264024 1204.339012 6712.852761 0.34761 1749.874046 97.732153 5.395645 5.557336 3.832032 139.117868 71.634543 70.001925 69.368166 68.748821 69.478602 69.529959 6.442428 64.016745
min 2.001166e+09 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.00000 0.000000 0.000000 0.000000 0.000000 0.000000 92.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
25% 2.001217e+09 9035.000000 555.000000 330.000000 14.000000 21.000000 0.000000 6692.000000 0.00000 103.000000 51.000000 3.000000 1.000000 4.000000 307.000000 41.000000 41.000000 38.000000 36.000000 23.000000 0.000000 6.000000 2.000000
50% 2.001263e+09 12635.000000 1304.000000 429.000000 18.000000 50.000000 0.000000 10393.000000 0.00000 524.000000 99.000000 6.000000 3.000000 7.000000 385.000000 81.000000 98.000000 79.000000 63.000000 63.000000 46.000000 10.000000 128.000000
75% 2.001315e+09 17050.000000 2661.000000 533.000000 21.000000 79.000000 188.000000 15185.000000 0.00000 1621.000000 169.000000 10.000000 7.000000 10.000000 484.000000 154.000000 152.000000 147.000000 145.000000 139.000000 117.000000 15.000000 130.000000
max 2.001401e+09 63095.000000 17232.000000 1187.000000 25.000000 112.000000 16195.000000 57065.000000 4.00000 13321.000000 1095.000000 43.000000 79.000000 40.000000 2500.000000 254.000000 254.000000 254.000000 254.000000 254.000000 254.000000 47.000000 132.000000

Como esperado, temos cerca que 10 jogadores para cada partida (não exatamente 10 por causa da existência de partidas com bots que não contam como jogadores na nossa base). Alguns dados importantes que conseguimos ver são, a média de 'kills', 'deaths' e 'assists' que compoem uma estatística chamada KDA, muito usada no jogo. A quantidade de gold e experiência por minuto também é um dado interessante, que pode interferir no resultado da partida. Também podemos usar a quantidade de last_hits e kills para ver quais personagens tem o maior número de vítimas em média por exemplo.

Seleção e projeção

Consulta: Quantidade de mensagens ao longo da partida

Nesta consulta ao banco de dados, verificamos o tempo em que os jogadores acessam o chat durante a partida para se comunicarem. Para isso, consultamos o atributo "time" da tabela "chat" de todas as partidas e plotamos na forma de um histograma, e como a duração das partidas varia, plotamos a duração média das partidas para efeito de referência, consultando o atributo "duration" da tabela "match", e fazendo a média dos valores da duração de todas as partidas.

In [15]:
df_chat = pd.read_sql_query(
'''
SELECT time
FROM chat
''', conn)

df_match_duration = pd.read_sql_query(
'''
SELECT AVG(duration)
FROM match
''', conn)

df_match_duration = df_match_duration.iloc[0][0]

display(df_chat.head())

plt.figure(figsize=(16,5))
plt.hist(df_chat['time'], bins=200)
plt.vlines(df_match_duration, 0, 1220, color='red')
plt.text(df_match_duration+50, 1200, 'Duração média das partidas')
plt.title("Quantidade de mensagens de texto ao longo das partidas", fontsize=20)
plt.show()
time
0 289
1 872
2 891
3 1391
4 1391

Consulta: Posição das wards colocadas pelos jogadores

Para esta consulta, selecionamos as coordenadas "x" e "y" do mapa em que as wards observadoras foram colocadas pelos jogadores em todas as partidas do nosso banco de dados, agrupamos as posições repetidas e somamos a quantidade de vezes que a ward foi posicionada nessas posições. Para isso realizamos uma seleção das posições e um "sum" das posições repetidas da tabela "obs" e demos um "group by" na posição "x" e "y". A partir dos dados gerados, plotamos um mapa de calor das posições das wards e, no fundo do mapa de calor colocamos a imagem do mapa do jogo, sendo possível assim visualizar as posições de wards mais utilizadas pelos jogadores nas partidas. Essa análise pode ser muito útil e determinante em uma partida pois comprando-se as sentry wards, que são as wards responsáveis por revelar as wards observadoras do time inimigo, e posicionando-as nesses locais, a chance de encontrar as wards inimigas é maior, o que possibilita detrui-las, e assim reduzir a "visão" do mapa pelo time inimigo.

In [16]:
df_obs = pd.read_sql_query(
'''
SELECT x_pos x, y_pos y, sum(obs_count) count 
FROM obs 
GROUP BY x_pos, y_pos
''', conn)

def dota2_heatmap(df, df_img, sizex, sizey, bins, name, title_height):
    img = plt.imread("Minimap_6.88.jpg")
    fig, ax = plt.subplots(figsize=(sizex,sizey))
    fig.suptitle(name, fontsize=28, y=title_height)
    ax.imshow(img, extent=[df_img['x'].min(), df_img['x'].max(), df_img['y'].min(), df_img['y'].max()])
    heatmap, xedges, yedges = np.histogram2d(df['x'], df['y'], bins=bins)
    extent = [xedges[0], xedges[-1], yedges[0], yedges[-1]]
    ax.imshow(heatmap.T, extent=extent, origin='lower', alpha=0.5, interpolation='bicubic')


dota2_heatmap(df_obs, df_obs, 15, 15, 35, 'Heatmap - Observer Wards', 0.9)
df_obs.head()
Out[16]:
x y count
0 66 130 1
1 66 132 2
2 68 130 2
3 68 132 7
4 70 114 1

Consulta: Posição nas lanes de acordo com cada time em uma partida específica

Para esta consulta fizemos duas seleções, uma para o time "Radiant" e outro para o "Dire", com o intuito de observar o posicionamento dos heróis em cada uma das equipes em uma partida de longa duração, específica da base de dados. Selecionamos as posições "x" e "y" visitadas por cada herói na partida e somamos as posições repetidas guardando-as em uma coluna separada, para assim mapearmos as posições mais visitadas pelos jogadores. Para identificarmos de qual time cada jogador pertence, utilizamos a informação do atributo "pos_player_slot", que identica de 0 a 4 os jogadores do time "Radiant" e de 128 a 132 os jogadores do time "Scourge". Para melhor visualização das posições mais visitadas pelos jogadores de cada time, plotamos dois mapas de calor com os dados, um para cada time.

In [17]:
df_lane_radiant = pd.read_sql_query(
'''
SELECT x_pos x, y_pos y, sum(pos_count) count
FROM lane_pos 
WHERE pos_player_slot < 5 AND pos_match_id = 2001310590
GROUP BY x_pos, y_pos
''', conn)

dota2_heatmap(df_lane_radiant, df_obs, 15, 15, 35, 'Heatmap - Radiant Lane Position', 0.84)

df_lane_dire = pd.read_sql_query(
'''
SELECT x_pos x, y_pos y, sum(pos_count) count
FROM lane_pos 
WHERE pos_player_slot > 127 AND pos_match_id = 2001310590
GROUP BY x_pos, y_pos
''', conn)


dota2_heatmap(df_lane_dire, df_obs, 15, 15, 35, 'Heatmap - Dire Lane Position', 0.86)

Junção de duas relações

Consulta: Estatísticas relacionadas ao resultado das partidas

Essa consulta mostra a média de várias estatísticas dos jogadores, de acordo com o resultado das partidas, tanto para a vitória quanto para a derrota, com o objetivo de visualizar e comparar características e estratégias individuais e coletivas que influenciam um time à vitória ou à derrota.

In [18]:
df_radiant = pd.read_sql_query(
'''
SELECT REPLACE(REPLACE(radiant_win,0,"Derrota"),1,"Vitória") Resultado , (AVG(kills) - AVG(deaths)) "Saldo de Vítimas", 
AVG(hero_healing), AVG(tower_damage), AVG(hero_damage), AVG(gold_per_min), AVG(gold_spent), AVG(xp_per_min), AVG(denies), AVG(last_hits), AVG(level)
FROM match AS M
INNER JOIN(
    SELECT player_match_id, player_slot, kills, deaths, hero_healing, tower_damage, hero_damage, gold_per_min, xp_per_min, denies, last_hits, level, gold_spent
    FROM player
    WHERE player_slot < 5
) AS P
ON M.match_id = P.player_match_id
GROUP BY Resultado
ORDER BY Resultado DESC
''', conn)

df_radiant
Out[18]:
Resultado Saldo de Vítimas AVG(hero_healing) AVG(tower_damage) AVG(hero_damage) AVG(gold_per_min) AVG(gold_spent) AVG(xp_per_min) AVG(denies) AVG(last_hits) AVG(level)
0 Vitória 2.607297 543.619459 2034.888378 12766.471216 468.944189 15396.437162 488.300946 4.900405 129.585405 18.460405
1 Derrota -3.519708 342.292409 418.122628 10321.974745 338.546715 11448.249635 380.027737 4.375182 115.694891 16.474307

Para ajudar a visualizar melhor os dados, podemos condensar algumas tabelas importantes em um único dataframe e gerar histogramas comparando os resultados das partidas.

Sabemos que em cada partida, existem 10 jogadores, 5 em cada um dos dois times, já que vamos ignorar as partidas com bots, então, uma maneira de organizar os dados é somando as estatísticas de cada time e adicionar essas estatísticas nas linhas de cada partida, separando as partidas por time, ou seja, onde antes existia uma entrada por partida, agora irá existir uma entrada por time para cada uma das partidas. Também adicionamos os nomes dos modos de jogo e tipo de lobby (casual, rankeado, etc).

In [28]:
df = pd.read_sql_query('''
SELECT "match_id" "Match ID", "game_mode_name" "Modo de jogo", "lobby_type_name" "Modo de lobby", "duration" "Duração", REPLACE(REPLACE("radiant_win",1,"Radiant"),0,"Dire") "Winner", "Team", "AVG(gold_spent)" "Ouro gasto em média",
"AVG(gold)" "Média de ouro sobrando ao final da partida", "AVG(xp_per_min)" "Média de experiência/min", "AVG(level)" "Média de nível dos personagens", "AVG(hero_healing)" "Média de cura",
"AVG(hero_damage)" "Média de dano nos heróis inimigos", "AVG(tower_damage)" "Média de dano nas torres inimigas", "AVG(last_hits)" "Last hits nas creeps", "sum(kills)" "Vítimas",
"sum(denies)" "Denies", "sum(deaths)" "Mortes", "AVG(gold_per_min)" "Média de ouro/min", "sum(assists)" "Assists"
, "tower_status_dire",
"tower_status_radiant", "barracks_status_radiant",
"barracks_status_dire", "first_blood_time"
FROM match AS m
INNER JOIN
    (SELECT player_match_id,REPLACE(REPLACE(sum(player_slot),10,"Radiant"),650,"Dire") Team,AVG(gold_spent),AVG(gold),AVG(xp_per_min),AVG(level),AVG(hero_healing),AVG(hero_damage),AVG(tower_damage),AVG(last_hits),sum(kills),sum(denies),sum(deaths),AVG(gold_per_min),sum(assists)
    FROM player
    WHERE player_slot < 5
    GROUP BY player_match_id
    UNION
    SELECT player_match_id,REPLACE(REPLACE(sum(player_slot),10,"Radiant"),650,"Dire") Team,AVG(gold_spent),AVG(gold),AVG(xp_per_min),AVG(level),AVG(hero_healing),AVG(hero_damage),AVG(tower_damage),AVG(last_hits),sum(kills),sum(denies),sum(deaths),AVG(gold_per_min),sum(assists)
    FROM player
    WHERE player_slot > 5
    GROUP BY player_match_id) AS p
ON m.match_id = p.player_match_id
INNER JOIN
    (SELECT id game_mode_id, name game_mode_name
    FROM game_mode) as gm
ON m.game_mode = gm.game_mode_id
INNER JOIN
    (SELECT id lobby_type_id, name lobby_type_name
    FROM lobby_type) as lt
ON m.lobby_type = lt.lobby_type_id
WHERE human_players = 10
''', conn)

pd.set_option('display.max_columns', 50)
df.head(10)
Out[28]:
Match ID Modo de jogo Modo de lobby Duração Winner Team Ouro gasto em média Média de ouro sobrando ao final da partida Média de experiência/min Média de nível dos personagens Média de cura Média de dano nos heróis inimigos Média de dano nas torres inimigas Last hits nas creeps Vítimas Denies Mortes Média de ouro/min Assists tower_status_dire tower_status_radiant barracks_status_radiant barracks_status_dire first_blood_time
0 2001166109 game_mode_all_draft lobby_type_ranked 1819 Radiant Dire 7347.0 714.0 309.8 12.8 57.2 9656.8 15.4 68.4 17 6 50 305.2 36 256 2047 63 48 0
1 2001166109 game_mode_all_draft lobby_type_ranked 1819 Radiant Radiant 13332.0 1873.4 501.8 16.4 1153.6 12929.0 2021.0 80.2 49 18 19 494.8 75 256 2047 63 48 0
2 2001166147 game_mode_captains_mode lobby_type_practice 1717 Radiant Dire 7171.0 822.0 313.2 12.6 906.0 6774.6 317.6 72.8 14 23 46 326.6 26 4 1958 63 3 3
3 2001166147 game_mode_captains_mode lobby_type_practice 1717 Radiant Radiant 12804.0 1682.6 475.8 15.4 407.0 13770.8 1616.4 75.0 46 35 16 505.8 91 4 1958 63 3 3
4 2001166161 game_mode_all_pick lobby_type_normal 2090 Dire Dire 14679.0 1948.0 517.6 18.0 334.6 9323.6 2502.8 115.0 33 12 26 495.2 49 1975 0 0 63 106
5 2001166161 game_mode_all_pick lobby_type_normal 2090 Dire Radiant 7844.0 874.8 369.0 15.0 316.8 9025.6 213.2 77.8 24 22 34 288.2 25 1975 0 0 63 106
6 2001166193 game_mode_all_draft lobby_type_ranked 2912 Radiant Dire 16413.0 1516.4 490.0 20.8 571.8 13433.2 775.0 203.4 44 45 38 409.6 56 4 1840 60 3 89
7 2001166193 game_mode_all_draft lobby_type_ranked 2912 Radiant Radiant 18293.0 1407.4 468.6 20.4 0.0 12676.6 2271.4 176.8 36 31 47 436.0 62 4 1840 60 3 89
8 2001166234 game_mode_all_pick lobby_type_normal 1945 Radiant Dire 5975.0 1839.8 341.8 13.8 0.0 8746.2 56.8 33.2 16 17 42 274.6 34 0 2047 63 0 197
9 2001166234 game_mode_all_pick lobby_type_normal 1945 Radiant Radiant 13348.0 2739.6 495.8 16.8 1098.0 10369.6 2012.4 89.8 42 15 20 488.4 56 0 2047 63 0 197

Vamos visualizar como é a proporção de vitórias por time

In [27]:
colors = ["#d66853", "#0081af"]
plt.figure(figsize=(10,5))
sns.countplot('Winner', data=df, palette=colors)
plt.title('Distribuição das vitórias por time', fontsize=16)
plt.xlabel('Resultado', fontsize=15)
plt.ylabel('Quantidade de vitórias', fontsize=15)
Out[27]:
Text(0, 0.5, 'Quantidade de vitórias')

Ainda utilizando a consulta anterior, podemos visualizar a diferença entre as estatísticas das vitórias e derrotas.

In [26]:
# Dataframe contendo apenas vitórias
df_wins = df[((df.Winner == 'Radiant') & (df.Team == 'Radiant')) | ((df.Winner == 'Dire') & (df.Team == 'Dire'))]

# Dataframe contendo apenas derrotas
df_losses = df[((df.Winner == 'Dire') & (df.Team == 'Radiant')) | ((df.Winner == 'Radiant') & (df.Team == 'Dire'))]

wins = df_wins.drop(['Match ID', 'Duração', 'tower_status_dire', 'tower_status_radiant', 'barracks_status_radiant', 'barracks_status_dire', 'first_blood_time'], axis=1)
losses = df_losses.drop(['Match ID', 'Duração', 'tower_status_dire', 'tower_status_radiant', 'barracks_status_radiant', 'barracks_status_dire', 'first_blood_time'], axis=1)

# fig, ax = plt.subplots(nrows=wins.shape[0], ncols=wins.shape[1])
i = 0
plt.figure(figsize=(22,15))
for col in wins.columns:
    if type(wins[col].iat[0]).__name__ == 'str':
        continue
    i+=1
    plt.subplot(4,4,i)
    plt.hist(wins[col], bins=18, color='#0081af', alpha=0.8, label="Vitória")
    plt.hist(losses[col], bins=18, color='#d66853', alpha=0.8, label="Derrota")
    plt.title(col)
    plt.legend()
    plt.show

Consulta: Quais são os itens mais usados no jogo?

Esta consulta verifica os itens utilizados pelos jogadores em todas as partidas com seus respectivos nomes, e soma a quantidade dos itens comprados em todas as partidas agrupando pelo nome de cada item, com o objetivo de verificar quais são os itens utilizáveis mais populares entre os jogadores.

In [29]:
df_mais_comprado = pd.read_sql_query(
'''
SELECT name, sum(item_count) total
FROM item_uses AS USES
INNER JOIN (
    SELECT name
    FROM item_ids
) AS IDS
ON USES.item_name = IDS.name
GROUP BY USES.item_name
ORDER BY total DESC
''', conn)

df_mais_comprado.head(10)
Out[29]:
name total
0 phase_boots 454569
1 bottle 382990
2 blink 199839
3 tpscroll 184620
4 power_treads 183446
5 tango 101080
6 magic_wand 98870
7 arcane_boots 92888
8 armlet 85297
9 ward_dispenser 58573

Consulta: Ordem das ações mais executadas no jogo

Esta consulta busca ordenar os comandos mais executados pelos jogadores em todas as partidas, representado em ordem decrescente. Para isso, coletamos os comandos executados por cada jogador individualmente e realizamos uma contagem em cima dos dados, agrupando por nome do comando. Foi concluído que o comando mais executado é o de andar, que é quando o jogador clica em uma posição no chão e o seu herói anda até lá.

In [30]:
df_actions = pd.read_sql_query(
'''
SELECT name, sum(action_count) action_count
FROM actions AS A
INNER JOIN(
    SELECT name, id
    FROM order_types
) AS O
ON A.action_id = O.id
GROUP BY name
ORDER BY action_count DESC
''', conn)

df_actions.head(10)
Out[30]:
name action_count
0 DOTA_UNIT_ORDER_MOVE_TO_POSITION 114197086
1 DOTA_UNIT_ORDER_ATTACK_TARGET 16910436
2 DOTA_UNIT_ORDER_CAST_NO_TARGET 3879493
3 DOTA_UNIT_ORDER_MOVE_TO_TARGET 2415730
4 DOTA_UNIT_ORDER_HOLD_POSITION 2179134
5 DOTA_UNIT_ORDER_CAST_TARGET 1836447
6 DOTA_UNIT_ORDER_ATTACK_MOVE 1656292
7 DOTA_UNIT_ORDER_CAST_POSITION 1655179
8 DOTA_UNIT_ORDER_PURCHASE_ITEM 901305
9 DOTA_UNIT_ORDER_TRAIN_ABILITY 498213

Junção de três ou mais relações

Consulta: Qual o herói com maior porcentagem de vitórias?

O objetivo desta consulta é verificar os heróis com maior taxa de vitória em todas partidas, utilizando o nome como fator de agrupamento dos dados. Para evitar heróis que estiveram pouco presente nas partidas, decidimos estabelecer que só entrariam heróis com mais de 100 partidas na base de dados, assim evitando dados enviesados, por exemplo, se um herói só foi escolhido uma vez e ganhou, sua taxa de vitória seria de 100%, mas isso não necessariamente condiz com a realidade. Para conseguirmos realizar esta consulta tivemos de realizar junção de três relações, tabelas "match", "player" e "heroes", e para calcular a taxa de vitória fizemos a soma do número de partidas vencedoras do herói dividido pelo número de partidas em que o herói foi selecionado.

In [32]:
df_winner_heroes = pd.read_sql_query(
'''
SELECT localized_name 'Hero Name', CAST(count(radiant_win) AS float)/CAST(M.num_matches AS float) WinRate
FROM match
INNER JOIN(
    SELECT player_match_id, localized_name, num_matches
    FROM player P
    INNER JOIN(
        SELECT hero_id, localized_name, num_matches
        FROM heroes
        INNER JOIN(
            SELECT hero_id n_id, count(hero_id) num_matches
            FROM player
            GROUP BY hero_id
        ) AS NUMP
        ON heroes.hero_id = NUMP.n_id
    ) AS H
    ON P.hero_id = H.hero_id
    WHERE player_slot < 5
) AS M
ON match_id = player_match_id
WHERE M.num_matches > 100
GROUP BY localized_name
ORDER BY WinRate DESC
''', conn)

df_winner_heroes.head(20)
Out[32]:
Hero Name WinRate
0 Enigma 0.568493
1 Razor 0.566038
2 Medusa 0.565217
3 Alchemist 0.561290
4 Bloodseeker 0.550898
5 Weaver 0.547619
6 Wraith King 0.543624
7 Kunkka 0.539535
8 Clockwerk 0.538813
9 Lion 0.533141
10 Slardar 0.533074
11 Luna 0.531646
12 Mirana 0.531056
13 Witch Doctor 0.530259
14 Vengeful Spirit 0.529412
15 Invoker 0.527473
16 Undying 0.526316
17 Silencer 0.525680
18 Winter Wyvern 0.524706
19 Shadow Fiend 0.522897

Consulta: Herói mais escolhido em partidas rankeadas

As partidas rankeadas possuem como característica um ambiente mais competitivo entre os times, pois o resultado da partida gera pontos que classificam cada jogador individualmente em um ranking de habilidades, onde o time ganhador e os jogadores que mais se sobressaíram podem subir de nível e ser melhor classificados de acordo com suas habilidades. Nesses tipos de partidas, as estratégias de equipe são mais bem pensadas e os jogadores escolhem heróis que julgam ter melhores performances individuais ou em equipe, consequentemente, o nível de dificuldade da partida aumenta. Diante deste contexto, esta consulta busca classificar a popularidade de escolha dos heróis para partidas rankeadas, do mais para o menos popular, e soma suas aparições nas partidas para melhor visualização.

In [33]:
df_most_picked_ranked = pd.read_sql_query(
'''
SELECT mode_name Modo, HR.hero_name Herói, count(HR.hero_name) Aparições
FROM match AS M
INNER JOIN(
    SELECT id, name mode_name 
    FROM lobby_type
    WHERE name = 'lobby_type_ranked'
) AS L
ON M.lobby_type = L.id
INNER JOIN(
    SELECT player_match_id, hero_id, hero_name
    FROM player AS P
    INNER JOIN(
        SELECT hero_id id, localized_name hero_name
        FROM heroes
    )AS H
    ON P.hero_id = H.id
)as HR
ON M.match_id = HR.player_match_id
GROUP BY HR.hero_name
ORDER BY Aparições DESC
''', conn)

df_most_picked_ranked.head()
Out[33]:
Modo Herói Aparições
0 lobby_type_ranked Shadow Fiend 513
1 lobby_type_ranked Windranger 464
2 lobby_type_ranked Invoker 339
3 lobby_type_ranked Tusk 320
4 lobby_type_ranked Earthshaker 317

Consulta: Last hits e Denies por Herói

Para os heróis conseguirem acumular dinheiro é necessário dar o golpe derradeiro em criaturas inimigas, criaturas neutras do mapa ou em heróis inimigos, esse golpe é chamado de "last hit". A forma de evitar que o herói inimigo dê o last hit em criaturas do seu time é o "deny", golpe derradeiro desferido pelo herói à criatura do próprio time, o que só pode ser feito se a criatura estiver com pouquíssima vida. No início do jogo, conseguir dar o last hit nas criaturas inimigas é fundamental para acumular dinheiro mais rapidamente e, por consequência, ter acesso a itens mais caros antes da equipe inimiga, o que pode garantir vantagem em batalhas.

Nesta consulta selecionamos os heróis e suas respectivas médias de last hits e denies por partida a partir do número total de partidas da nossa base de dados, e ordenamos de forma decrescente a média de last hits dos heróis identificados aqui pelo nome, para demonstrar aqueles que possuem melhor desempenho na média.

In [34]:
df = pd.read_sql_query(
'''
SELECT localized_name "Hero Name", sum(last_hits)/count(localized_name) "Total Last Hits", sum(denies)/count(localized_name) "Total Denies"
FROM match AS M
INNER JOIN(
    SELECT id, name mode_name 
    FROM lobby_type
    WHERE name = 'lobby_type_ranked'
) AS L
ON M.lobby_type = L.id
INNER JOIN(
    SELECT player_match_id, localized_name, last_hits, denies
    FROM player P
    INNER JOIN(
        SELECT hero_id id ,localized_name
        FROM heroes
    ) AS H
    ON P.hero_id = H.id
) AS M
ON match_id = player_match_id
GROUP BY "Hero Name"
ORDER BY "Total Last Hits" DESC
''', conn)

df.head(10)
Out[34]:
Hero Name Total Last Hits Total Denies
0 Anti-Mage 281 10
1 Medusa 272 7
2 Alchemist 252 3
3 Shadow Fiend 245 14
4 Nature's Prophet 235 1
5 Naga Siren 224 4
6 Luna 222 7
7 Juggernaut 219 8
8 Ember Spirit 214 5
9 Gyrocopter 212 9

Consulta: Proporção de Vítimas e Assistencias por Mortes para cada Herói. Qual o herói com maior KDA?

Uma forma de analisar o desempenho dos heróis na partida é analisar o seu KDA (kill, deaths, assists), quanto maior o seu KDA, melhor desempenho o herói teve na partida. Para se calcular esse índice devemos seguir essa fórmula:

KDA = $(kills + assists)/MAX(1,deaths).$

Nesta consulta, calculamos o KDA de cada herói e fizemos a média em todas as partidas da base, e para melhor visualização dos melhores heróis de acordo com esse índice, ordenamos de forma descrescente a lista com todos os heróis do banco.

In [36]:
df_kda = pd.read_sql_query(
'''
SELECT hero_name 'Hero Name', sum(kills) Kills, sum(assists) Assists, sum(deaths) Deaths, (CAST(sum(kills) AS float)+CAST(sum(assists) AS float))/CAST(sum(deaths) AS float) "KDA Ratio"
FROM player AS P
INNER JOIN(
    SELECT hero_id id, localized_name hero_name
    FROM heroes
) AS H
ON P.hero_id = H.id
GROUP BY hero_id
ORDER BY "KDA Ratio" DESC
''', conn)

df_kda.head(10)
Out[36]:
Hero Name Kills Assists Deaths KDA Ratio
0 Spectre 2216 4044 1493 4.192900
1 Zeus 2689 4862 2050 3.683415
2 Medusa 1044 1828 848 3.386792
3 Weaver 1559 1868 1058 3.239130
4 Necrophos 2575 4338 2137 3.234909
5 Brewmaster 523 887 436 3.233945
6 Queen of Pain 4507 4835 2930 3.188396
7 Ember Spirit 4766 6208 3453 3.178106
8 Riki 3341 2394 1830 3.133880
9 Wraith King 2099 3186 1692 3.123522

Consulta: Dano médio em torres de cada herói

O objetivo do jogo é destruir a torre "ancient" do time inimigo, para isso, é necessário que, no mínimo, todas as torres inimigas de uma das lanes (bot, mid, top) do mapa sejam completamente destruídas.

Então um fator que pode ser determinante para se vencer a partida é possuir heróis que desfiram golpes e magias que causem grande quantidade de dano nas torres inimigas.

Nesta consulta buscamos identificar os heróis que causam mais dano em média nas torres inimigas, e para enriquecer as características da consulta e dos heróis, selecionamos o atributo primário do herói (força, agilidade ou inteligência) e também sua forma de ataque (melee ou ranged).

In [38]:
df_kda = pd.read_sql_query(
'''
SELECT hero_name "Hero Name", primary_attr "Primary Attribute", attack_type "Attack Type", AVG(tower_damage) "Average Tower Damage per Match"
FROM player AS P
INNER JOIN(
    SELECT hero_id id, localized_name hero_name, primary_attr, attack_type
    FROM heroes
)AS H
ON P.hero_id = H.id
GROUP BY hero_id
ORDER BY "Average Tower Damage per Match" DESC
''', conn)

df_kda.head(10)
Out[38]:
Hero Name Primary Attribute Attack Type Average Tower Damage per Match
0 Lycan str Melee 3970.547368
1 Terrorblade agi Melee 3458.318681
2 Drow Ranger agi Ranged 3034.726368
3 Clinkz agi Ranged 3011.193333
4 Shadow Fiend agi Ranged 2936.516507
5 Lone Druid agi Ranged 2926.788732
6 Luna agi Ranged 2861.107595
7 Anti-Mage agi Melee 2808.364066
8 Nature's Prophet int Ranged 2734.181373
9 Juggernaut agi Melee 2707.572881